﻿using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Microsoft.Win32;
using System.ComponentModel;
using System.IO;
using System.Windows.Forms;


namespace eMisc
{
    public class WorkSheetUtils
    {
        #region Clear

        static void ClearShapes(Worksheet ws)
        {
            while (ws.Shapes.Count > 0)
            {
                Shape i = ws.Shapes.Item(1);
                i.Delete();
            }
        }
        static void ClearData(Worksheet ws)
        {
            ws.Cells.Value2 = null;
        }
        static void ClearBorders(Worksheet ws)
        {
            ws.Cells.Borders.LineStyle = XlLineStyle.xlLineStyleNone;
        }
        static void DefaultColRow(Worksheet ws)
        {
            ws.Cells.EntireRow.AutoFit();
            ws.Cells.EntireColumn.ColumnWidth = 8.43;
        }
        static void clearSilent(Worksheet ws)
        {
            ClearShapes(ws);
            ClearData(ws);
            ClearBorders(ws);
            DefaultColRow(ws);
        }


        /// <summary>
        /// процедура для BackGroundWorker
        /// </summary>
        /// <param name="args"></param>
        /// <param name="ws"></param>
        /// <param name="worker"></param>
        /// <param name="e"></param>
        static void proc_clear(Dictionary<string, object> args,
                Worksheet ws,
                BackgroundWorkerEx.BackgroundWorkerEx worker, DoWorkEventArgs e)
        {
            int _steps = 4;
            worker.ReportProgress(0);
            int _step = 1;
            worker.StepCaption = "Очистка содержимого листа";
            //---
            if (worker.CancellationPending) e.Cancel = true;
            else
            {
                worker.ReportProgress((int)(((float)_step / (float)_steps) * 100));
                ClearShapes(ws);
                if (worker.CancellationPending) e.Cancel = true;
                else
                {
                    _step++;
                    worker.ReportProgress((int)(((float)_step / (float)_steps) * 100));
                    ClearData(ws);
                    if (worker.CancellationPending) e.Cancel = true;
                    else
                    {
                        _step++;
                        worker.ReportProgress((int)(((float)_step / (float)_steps) * 100));
                        ClearBorders(ws);
                        if (worker.CancellationPending) e.Cancel = true;
                        else
                        {
                            _step++;
                            worker.ReportProgress((int)(((float)_step / (float)_steps) * 100));
                            ClearBorders(ws);
                        }
                    }
                }
            }
        }

        public static bool Clear(Worksheet ws, bool Silent)
        {
            bool res = true;
            if (Silent)
                clearSilent(ws);
            else
                res = frmBG.frmBG.ProcessWorkSheet("Очистка листа", proc_clear, null, ws);
            return res;
        }


        #endregion Clear

        public static string NCell(int i)
        {
            if (i <= 0 || i > 256)
                throw new Exception("Неверный индекс ячейки: " + i.ToString());
            switch (i)
            {
                case 1: return "A";
                case 2: return "B";
                case 3: return "C";
                case 4: return "D";
                case 5: return "E";
                case 6: return "F";
                case 7: return "G";
                case 8: return "H";
                case 9: return "I";
                case 10: return "J";
                case 11: return "K";
                case 12: return "L";
                case 13: return "M";
                case 14: return "N";
                case 15: return "O";
                case 16: return "P";
                case 17: return "Q";
                case 18: return "R";
                case 19: return "S";
                case 20: return "T";
                case 21: return "U";
                case 22: return "V";
                case 23: return "W";
                case 24: return "X";
                case 25: return "Y";
                case 26: return "Z";
                default:
                    int r;
                    int d = Math.DivRem(i, 26, out r);
                    return NCell(d)+NCell(i-d*26);
            }
        }
        public static string RC(int i, int j)
        {
            return NCell(i) + j.ToString();
        }

        public static bool CheckCell(ref string cell,out string err)
        {
            err="";
            string s = "";
            foreach (char c in cell.Replace(" ", ""))
                if (char.IsLetterOrDigit(c)) s += c;
            if (s != "")
            {
                int firstDigit = -1;
                string sl="";
                for (int i = 0; i < s.Length; i++)
                {
                    if (char.IsDigit(s, i))
                    {
                        firstDigit = i;
                        break;
                    }
                    else
                        if (char.IsLetter(s, i))
                            sl += s[i];
                }
                if (sl == "")
                    err = "Неверно указан формат ячейки" + Environment.NewLine +
                          "(не найдено название колонки)";
                else
                    if (firstDigit == -1)
                        err = "Неверно указан формат ячейки" + Environment.NewLine +
                              "(не найден индекс строки)";
                    else
                    {
                        string sr = s.Substring(firstDigit);
                        foreach (char c in sr)
                            if (!char.IsDigit(c))
                            {
                                err = "Неверно указан формат ячейки" + Environment.NewLine +
                                      "(ошибка при указании индекса строки)";
                                break;
                            }
                    }
                if (err == "") cell = s;
            }
            return err=="";
        }

        public static bool CheckCol(string col,out string err)
        {
            err="";
            foreach(char c in col.Trim())
                if (!char.IsLetter(c))
                {
                    err = "Неверно указан формат колонки";
                    break;
                }
            return err=="";
        }


        public static void _prepare(Worksheet ws)
        {
            ws.Cells.Font.Name = "Arial";
            ws.Cells.Font.Size = 8;
        }

        /// <summary>
        /// список листов в excel-книге
        /// </summary>
        /// <param name="book">имя файла</param>
        /// <param name="sheets"></param>
        /// <param name="err"></param>
        public static void GetSheetNames(string book, List<string> sheets, out string err)
        {
            sheets.Clear(); err = "";
            ApplicationClass a = new ApplicationClass();
            a.DisplayAlerts = false;
            try
            {
                Workbook wb =
                    a.Workbooks.Open(book,
                        XlUpdateLinks.xlUpdateLinksNever,
                        true, //readOnly
                        Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                        Type.Missing, Type.Missing);

                foreach (Worksheet w in wb.Worksheets)
                    sheets.Add(w.Name);
                wb.Close(false, Type.Missing, false);
                Marshal.FinalReleaseComObject(wb);
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
            }
            catch (Exception e)
            {
                err = "Ошибка при открытии шаблона" + Environment.NewLine +
                    e.Message;
            }
            a.Quit();
            Marshal.FinalReleaseComObject(a);
            GC.Collect();
            GC.WaitForPendingFinalizers();
            GC.Collect();
        }

        public static void GetSheetNames(string book, System.Windows.Forms.ComboBox.ObjectCollection sheets, out string err)
        {
            List<string> l = new List<string>();
            GetSheetNames(book, l, out err);
            sheets.Clear();
            sheets.AddRange(l.ToArray());
        }

        /// <summary>
        /// копирует в книгу bookTo (в конец) лист tmpSheet из книги-файл tmpBook
        /// </summary>
        /// <param name="bookTo"></param>
        /// <param name="tmpBook"></param>
        /// <param name="tmpSheet"></param>
        /// <returns></returns>
        public static bool CopyTemplate(Workbook bookTo, string tmpBook, string tmpSheet, out string err)
        {
            err = "";
            if (string.IsNullOrEmpty(tmpBook)) err = "Не указан файл шаблона";
            else
            {
                if (!File.Exists(tmpBook)) err = "Не найден файл шаблона: " + tmpBook;
                else
                {
                    if (string.IsNullOrEmpty(tmpSheet)) err = "Не указан лист в шаблоне";
                    else
                    {
                        bookTo.Application.DisplayAlerts = false;
                        try
                        {
                            Workbook wb =
                                bookTo.Application.Workbooks.Open(tmpBook,
                                    XlUpdateLinks.xlUpdateLinksNever,
                                    true, //readOnly
                                    Type.Missing, Type.Missing,
                                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                    Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                    Type.Missing, Type.Missing);
                            bookTo.Application.ActiveWindow.WindowState = XlWindowState.xlMinimized;
                            bool fnd = false;
                            foreach (Worksheet w in wb.Worksheets)
                            {
                                if (w.Name.ToLower() == tmpSheet.ToLower())
                                {
                                    w.Copy(Type.Missing, bookTo.Worksheets[bookTo.Worksheets.Count]);
                                    fnd = true;
                                    break;
                                }
                            }
                            if (!fnd) err = "Не найден лист в шаблоне: " + tmpSheet;
                            wb.Close(false, Type.Missing, false);
                            Marshal.FinalReleaseComObject(wb);
                            GC.Collect();
                            GC.WaitForPendingFinalizers();
                            GC.Collect();
                        }
                        catch (Exception e)
                        {
                            err = "Ошибка при открытии шаблона" + Environment.NewLine +
                                e.Message;
                        }
                    }
                }
            }
            return err == "";
        }

        /// <summary>
        /// копирует в книгу bookTo (в конец) листы tmpSheets[] из книги-файл tmpBook
        /// out int[] okLoad - индексы листов (размерность массива как у tmpSheets[])
        /// </summary>
        /// <param name="bookTo"></param>
        /// <param name="tmpBook"></param>
        /// <param name="tmpSheet"></param>
        /// <returns></returns>
        public static bool CopyTemplateAndVisualize(Workbook bookTo, string tmpBook, string[] tmpSheets, out int[] okLoad,
            out string err)
        {
            err = "";
            okLoad = new int[tmpSheets.Length];
            for (int i = 0; i < okLoad.Length; i++) okLoad[i] = -1;

            if (string.IsNullOrEmpty(tmpBook)) err = "Не указан файл шаблона";
            else
            {
                if (!File.Exists(tmpBook)) err = "Не найден файл шаблона: " + tmpBook;
                else
                {
                    bookTo.Application.DisplayAlerts = false;
                    try
                    {
                        Workbook wb =
                            bookTo.Application.Workbooks.Open(tmpBook,
                                XlUpdateLinks.xlUpdateLinksNever,
                                true, //readOnly
                                Type.Missing, Type.Missing,
                                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                Type.Missing, Type.Missing);
                        bookTo.Application.ActiveWindow.WindowState = XlWindowState.xlMinimized;
                        for (int i = 0; i < tmpSheets.Length; i++)
                        {
                            string tmpSheet = tmpSheets[i];
                            if (tmpSheet != "")
                            {
                                foreach (Worksheet w in wb.Worksheets)
                                    if (w.Name.ToLower() == tmpSheet.ToLower())
                                    {
                                        w.Copy(Type.Missing, bookTo.Worksheets[bookTo.Worksheets.Count]);
                                        okLoad[i] = bookTo.Worksheets.Count;
                                        break;
                                    }
                            }
                        }
                        wb.Close(false, Type.Missing, false);
                        Marshal.FinalReleaseComObject(wb);
                        GC.Collect();
                        GC.WaitForPendingFinalizers();
                        GC.Collect();
                    }
                    catch (Exception e)
                    {
                        err = "Ошибка при открытии шаблона" + Environment.NewLine +
                            e.Message;
                    }
                }
            }
            return err == "";
        }


        ///// <summary>
        ///// копирует в книгу bookTo (в конец) листы tmpSheets[] из книги-файл tmpBook
        ///// out int[] okLoad - индексы листов (размерность массива как у tmpSheets[])
        ///// </summary>
        ///// <param name="bookTo"></param>
        ///// <param name="tmpBook"></param>
        ///// <param name="tmpSheet"></param>
        ///// <returns></returns>
        //public static bool CopyTemplate(Workbook bookTo, string tmpBook, string[] tmpSheets, out int[] okLoad, 
        //    out string err)
        //{
        //    err = "";
        //    okLoad = new int[tmpSheets.Length];
        //    for (int i = 0; i < okLoad.Length; i++) okLoad[i] = -1;

        //    if (string.IsNullOrEmpty(tmpBook)) err = "Не указан файл шаблона";
        //    else
        //    {
        //        if (!File.Exists(tmpBook)) err = "Не найден файл шаблона: " + tmpBook;
        //        else
        //        {
        //            bookTo.Application.DisplayAlerts = false;
        //            try
        //            {
        //                Workbook wb =
        //                    bookTo.Application.Workbooks.Open(tmpBook,
        //                        XlUpdateLinks.xlUpdateLinksNever,
        //                        true, //readOnly
        //                        Type.Missing, Type.Missing,
        //                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        //                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
        //                        Type.Missing, Type.Missing);
        //                bookTo.Application.ActiveWindow.WindowState = XlWindowState.xlMinimized;
        //                for (int i = 0; i < tmpSheets.Length; i++)
        //                {
        //                    string tmpSheet = tmpSheets[i];
        //                    if (tmpSheet != "")
        //                    {
        //                        foreach (Worksheet w in wb.Worksheets)
        //                            if (w.Name.ToLower() == tmpSheet.ToLower())
        //                            {
        //                                w.Copy(Type.Missing, bookTo.Worksheets[bookTo.Worksheets.Count]);
        //                                okLoad[i] = bookTo.Worksheets.Count;
        //                                break;
        //                            }
        //                    }
        //                }
        //                wb.Close(false, Type.Missing, false);
        //                Marshal.FinalReleaseComObject(wb);
        //                GC.Collect();
        //                GC.WaitForPendingFinalizers();
        //                GC.Collect();
        //            }
        //            catch (Exception e)
        //            {
        //                err = "Ошибка при открытии шаблона" + Environment.NewLine +
        //                    e.Message;
        //            }
        //        }
        //    }
        //    return err == "";
        //}

        public static bool SheetExist(string name, Workbook wb, out Worksheet ws)
        {
            bool res = false; ws = null;
            foreach (Worksheet w in wb.Worksheets)
                if (w.Name.ToLower() == name.ToLower())
                {
                    ws = w;
                    res = true;
                    break;
                }
            return res;
        }

        public static bool SheetExist(string name, Workbook wb)
        {
            bool res = false;
            foreach (Worksheet w in wb.Worksheets)
                if (w.Name.ToLower() == name.ToLower())
                {
                    res = true;
                    break;
                }
            return res;
        }

        /// <summary>
        /// название листа
        /// (длина должна быть меньше 31, совпадения не допускаются)
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public static string SetSheetName(string name)
        {
            const int maxLength=31;
            string res = name;
            if (res.Length > maxLength)
                res = res.Substring(0, maxLength - 3) + "...";
            return res;
        }
    }

    public static class TemplateXlsMisc
    {
        static string _headerBegin = "<header>";
        static string _headerEnd = "</header>";
        static string _footerBegin = "<footer>";
        static string _footerEnd = "</footer>";
        static string _dataBegin = "<data>";
        static string _dataEnd = "</data>";

        public static bool getHeaders(Worksheet w, List<TemplateXlsItem> L,out int rFrom,out int rTo)
        {
            return getList(w, L,out rFrom,out rTo, 0, _headerBegin, _headerEnd);
        }
        public static bool getFooters(Worksheet w, List<TemplateXlsItem> L,out int rFrom,out int rTo,
            int rowSearchFrom)
        {
            return getList(w, L,out rFrom,out rTo, rowSearchFrom, _footerBegin, _footerEnd);
        }
        public static bool getDatas(Worksheet w, List<TemplateXlsItem> L,out int rFrom,out int rTo,
            int rowSearchFrom)
        {
            return getList(w, L,out rFrom,out rTo, rowSearchFrom, _dataBegin, _dataEnd);
        }

        static bool getList(Worksheet w,List<TemplateXlsItem> L, 
            out int rFrom,out int rTo,
            int rowSearchFrom,
            string _beginTag, string _endTag)
        {
            L.Clear();
            rFrom = 0; rTo = 0;
            Range R = w.UsedRange;
            int cTo = R.Columns.Count;
            for(int r=Math.Max(rowSearchFrom,R.Row);r<R.Row+R.Rows.Count;r++)
            {
                object v = w.get_Range(WorkSheetUtils.RC(1,r),WorkSheetUtils.RC(1,r)).Value2;
                if (v != null)
                {
                    if (v.ToString().ToLower() == _beginTag) rFrom = r;
                    if (v.ToString().ToLower() == _endTag)
                    {
                        rTo = r;
                        if (rFrom == 0) break;//mistake
                    }
                    if (rFrom != 0 && rTo != 0) break;
                }
            }
            if (rFrom != 0 && rTo != 0)
            {
                //сканируем область [rFrom+1,1]...[rTo-1,cTo]
                for (int r = rFrom + 1; r < rTo; r++)
                    for (int c = 1; c <= cTo; c++)
                    {
                        string _cell = WorkSheetUtils.RC(c, r);
                        object v = w.get_Range(_cell, _cell).Value2;
                        if (v != null)
                        {
                            if (v.ToString().StartsWith("<") && v.ToString().EndsWith(">"))
                            {
                                string pn = v.ToString().Substring(1);
                                pn=pn.Substring(0,pn.Length-1);
                                TemplateXlsItem ti;
                                ti.cell = _cell;
                                ti.paramName = pn;
                                L.Add(ti);
                            }
                        }
                    }
            }
            return L.Count > 0;
        }
    }

    public struct TemplateXlsItem
    {
        public string cell, paramName;
    }
}
